# TODO: create PM image from aod and temp (PM regression)
# 
# Author: phamha
###############################################################################

#import library
library(gstat)
library(base)
library(RPostgreSQL)
library(stringr)
library(raster)
library(gdalUtils)
#library(rgdal)
library(rPython)


host_name = '112.137.129.222'
database_name = 'apom'
user_name = 'postgres'
password = 'postgres'


time_query = "select aqstime from res.satresampviirs where filename like '%"

tif2raster_file = "/home/phamha/Desktop/tif2rasref.py"
insert_mod_query = "INSERT INTO prodviirs.prodviirspm_vn_collection1(aqstime, rasref, filename, filepath, gridid,pmid,max,min,avg, type) VALUES ('"

#get data from DB
getDataFromDB = function(sql_command){
	driver = dbDriver("PostgreSQL")
	connect = dbConnect(driver,dbname = database_name,host = host_name,port=5432,user = user_name,password= password)
	rs = dbSendQuery(connect,sql_command)
	data=fetch(rs,n=-1)
	dbDisconnect(connect)
	dbUnloadDriver(driver)
	return (data)
}
insertDataToDB = function(sql_command){
	driver = dbDriver("PostgreSQL")
	connect = dbConnect(driver,dbname = database_name,host = host_name,port=5432,user = user_name,password= password)
	rs = dbSendQuery(connect,sql_command)
	dbDisconnect(connect)
	dbUnloadDriver(driver)
}

insert_uk_images = function(uk_file,sat_data){
	
	python.assign("raster_file",uk_file)
	python.load(tif2raster_file)
	raster_ref = python.get("raster_ref")
	
	uk_raster = raster(uk_file)
	uk_value = values(uk_raster)
	uk_value = uk_value[uk_value!=-9999]
	max_value = max(uk_value,na.rm = TRUE)
	min_value = min(uk_value,na.rm = TRUE)
	avg_value = mean(uk_value,na.rm = TRUE)
	
	start_index = regexpr("apom",uk_file)
	mid_index = regexpr("G[^G]*$",uk_file)
	end_index = nchar(uk_file)	
	uk_file_path = substr(uk_file,start_index,mid_index-1)
	uk_file_name = substr(uk_file,mid_index,end_index)
	search_file_name = substr(uk_file,mid_index,end_index-8)
	print(uk_file_name)
	
	time_query = paste(time_query,search_file_name,"%'",sep="")
	data = getDataFromDB(time_query)
	
	mod04_aqstime = data$aqstime[1]
	aqstime = strptime(mod04_aqstime,format="%Y-%m-%d %H:%M:%S")
	aqstime = aqstime + 25200	
	month = format.Date(aqstime,"%m")
	year = format.Date(aqstime,"%Y")
	
	query = paste(insert_mod_query,aqstime,"'::timestamp, '",raster_ref,"'::raster, '",uk_file_name,"', '",uk_file_path,"', 1, 1, ",max_value,", ",min_value,", ",avg_value,", ",1,")",sep="")
	insertDataToDB(query)
	#print(query)
	#print ("Insert to database good!");
	print(aqstime)
	print(uk_file_name)


}


## uk_file = "/var/www/html/fimo/apom/prod/ProdVIIRS/2012/GAERO-VAOOO_npp_d20121001_t0602445_e0608248_b04808_c20140121181330543019_noaa_ops_550_resample/GAERO-VAOOO_npp_d20121001_t0602445_e0608248_b04808_c20140121181330543019_noaa_ops_550_resample_uk.tif"
## insert_uk_images(uk_file,"mod")
inputPath = "/var/www/html/fimo/apom/prod/ProdVIIRS"
listFile = list.files(path = inputPath,pattern="uk.tif$",full.names= TRUE,recursive=TRUE)
listSize = length(listFile)
for(i in 1:listSize){
	insert_uk_images(listFile[i],"mod")
}
print("done")




